﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Text;
using System.IO;
using System.Configuration;

namespace DataCollectService
{
    public abstract class DbHelperSQL
    {
        //<add key="MSSqlConnString" value="Data Source=mes.hzyc.org.cn;Initial Catalog=ST1_BSCockpit;User Id=sa;Password=hzyc)(*765" />
        public static string __connectionString = "Data Source=" + DataBaseHelper.IP + ";Initial Catalog=" + DataBaseHelper.ERPDBName + ";User Id=" + DataBaseHelper.SqlAccountName + ";Password=" + DataBaseHelper.SqlAccountPwd + "";
        public static string __connectionStringCrm = "";
        public static string connectionString
        {
            get
            {
                __connectionString = "Data Source=" + DataBaseHelper.IP + ";Initial Catalog=" + DataBaseHelper.ERPDBName + ";User Id=" + DataBaseHelper.SqlAccountName + ";Password=" + DataBaseHelper.SqlAccountPwd + "";
                return __connectionString;
            }
        }
        public static string connectionStringCrm
        {
            get
            {
                return __connectionStringCrm;
            }
        }
        public DbHelperSQL() { }

        #region 公用方法
        /// <summary>
        /// 判断是否存在某表的某个字段
        /// </summary>
        /// <param name="tableName">表名称</param>
        /// <param name="columnName">列名称</param>
        /// <returns>是否存在</returns>
        public static bool ColumnExists(string tableName, string columnName)
        {
            string sql = "select count(1) from syscolumns where [id]=object_id('" + tableName + "') and [name]='" + columnName + "'";
            object res = GetSingle(sql);
            if (res == null)
            {
                return false;
            }
            return Convert.ToInt32(res) > 0;
        }
        public static int GetMinID(string FieldName, string TableName)
        {
            string strsql = "select min(" + FieldName + ") from " + TableName;
            object obj = DbHelperSQL.GetSingle(strsql);
            if (obj == null)
            {
                return 0;
            }
            else
            {
                return int.Parse(obj.ToString());
            }
        }
        public static int GetMaxID(string FieldName, string TableName)
        {
            string strsql = "select max(" + FieldName + ")+1 from " + TableName;
            object obj = DbHelperSQL.GetSingle(strsql);
            if (obj == null)
            {
                return 1;
            }
            else
            {
                return int.Parse(obj.ToString());
            }
        }
        public static bool Exists(string strSql)
        {
            object obj = DbHelperSQL.GetSingle(strSql);
            int cmdresult;
            if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
            {
                cmdresult = 0;
            }
            else
            {
                cmdresult = int.Parse(obj.ToString());
            }
            if (cmdresult == 0)
            {
                return false;
            }
            else
            {
                return true;
            }
        }
        /// <summary>
        /// 表是否存在
        /// </summary>
        /// <param name="TableName"></param>
        /// <returns></returns>
        public static bool TabExists(string TableName)
        {
            string strsql = "select count(*) from sysobjects where id = object_id(N'[" + TableName + "]') and OBJECTPROPERTY(id, N'IsUserTable') = 1";
            //string strsql = "SELECT count(*) FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[" + TableName + "]') AND type in (N'U')";
            object obj = DbHelperSQL.GetSingle(strsql);
            int cmdresult;
            if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
            {
                cmdresult = 0;
            }
            else
            {
                cmdresult = int.Parse(obj.ToString());
            }
            if (cmdresult == 0)
            {
                return false;
            }
            else
            {
                return true;
            }
        }
        public static bool Exists(string strSql, params SqlParameter[] cmdParms)
        {
            object obj = DbHelperSQL.GetSingle(strSql, cmdParms);
            int cmdresult;
            if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
            {
                cmdresult = 0;
            }
            else
            {
                cmdresult = int.Parse(obj.ToString());
            }
            if (cmdresult == 0)
            {
                return false;
            }
            else
            {
                return true;
            }
        }
        #endregion

        #region  执行简单SQL语句
        /// <summary>
        /// 执行查询语句，返回DataSet
        /// </summary>
        /// <param name="SQLString">查询语句</param>
        /// <returns>DataSet</returns>
        public static int ExecuteSql(string SQLString, List<string[]> sqlparms)
        {
            string msg = "";
            return ExecuteSql(SQLString, ref msg, sqlparms);
        }
        /// <summary>
        /// 执行查询语句，返回DataSet
        /// </summary>
        /// <param name="SQLString">查询语句</param>
        /// <returns>DataSet</returns>
        public static int ExecuteSql(string SQLString, ref string msg, List<string[]> sqlparms)
        {
            try
            {
                using (SqlConnection connection = new SqlConnection(connectionString))
                {
                    using (SqlCommand cmd = new SqlCommand(SQLString, connection))
                    {
                        try
                        {
                            connection.Open();
                            PrepareCommand(cmd, connection, null, SQLString, getparms(sqlparms));
                            //cmd.CommandTimeout = time;
                            int rows = cmd.ExecuteNonQuery();
                            return rows;
                        }
                        catch (System.Data.SqlClient.SqlException e)
                        {
                            connection.Close();
                            //throw e;
                            msg = e.Message;
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                msg = ex.Message;
            }
            return -999999;
        }

        public static int ExecuteSql(string SQLString, ref string msg, List<string[]> sqlparms,string conStr)
        {
            try
            {
                using (SqlConnection connection = new SqlConnection(conStr))
                {
                    using (SqlCommand cmd = new SqlCommand(SQLString, connection))
                    {
                        try
                        {
                            connection.Open();
                            PrepareCommand(cmd, connection, null, SQLString, getparms(sqlparms));
                            //cmd.CommandTimeout = time;
                            int rows = cmd.ExecuteNonQuery();
                            return rows;
                        }
                        catch (System.Data.SqlClient.SqlException e)
                        {
                            connection.Close();
                            //throw e;
                            msg = e.Message;
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                msg = ex.Message;
            }
            return -999999;
        }



        /// <summary>
        /// 执行SQL语句，返回影响的记录数
        /// </summary>
        /// <param name="SQLString">SQL语句</param>
        /// <param name="time">秒为单位</param>
        /// <returns>影响的记录数</returns>
        public static int ExecuteSql(string SQLString, int time)
        {
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = new SqlCommand(SQLString, connection))
                {
                    try
                    {
                        connection.Open();
                        cmd.CommandTimeout = time;
                        int rows = cmd.ExecuteNonQuery();
                        return rows;
                    }
                    catch (System.Data.SqlClient.SqlException e)
                    {
                        connection.Close();
                        throw e;
                    }
                }
            }
        }

        public static int ExecuteSql(string SQLString, int time,string conStr)
        {
            using (SqlConnection connection = new SqlConnection(conStr))
            {
                using (SqlCommand cmd = new SqlCommand(SQLString, connection))
                {
                    try
                    {
                        connection.Open();
                        cmd.CommandTimeout = time;
                        int rows = cmd.ExecuteNonQuery();
                        return rows;
                    }
                    catch (System.Data.SqlClient.SqlException e)
                    {
                        connection.Close();
                        throw e;
                    }
                }
            }
        }

        /// <summary>
        /// 执行SQL语句，返回影响的记录数
        /// </summary>
        /// <param name="SQLString">SQL语句</param>
        /// <returns>影响的记录数</returns>
        public static int ExecuteSql(string SQLString)
        {
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = new SqlCommand(SQLString, connection))
                {
                    try
                    {
                        connection.Open();
                        int rows = cmd.ExecuteNonQuery();
                        return rows;
                    }
                    catch (System.Data.SqlClient.SqlException e)
                    {
                        connection.Close();
                        throw e;
                    }
                }
            }
        }
        public static int ExecuteSql(string SQLString,string conStr)
        {
            using (SqlConnection connection = new SqlConnection(conStr))
            {
                using (SqlCommand cmd = new SqlCommand(SQLString, connection))
                {
                    try
                    {
                        connection.Open();
                        int rows = cmd.ExecuteNonQuery();
                        return rows;
                    }
                    catch (System.Data.SqlClient.SqlException e)
                    {
                        connection.Close();
                        throw e;
                    }
                }
            }
        }

        /// <summary>
        /// 2012-2-21新增重载，执行SQL语句，返回影响的记录数
        /// </summary>
        /// <param name="connection">SqlConnection对象</param>
        /// <param name="trans">SqlTransaction事件</param>
        /// <param name="SQLString">SQL语句</param>
        /// <returns>影响的记录数</returns>
        public static int ExecuteSql(SqlConnection connection, SqlTransaction trans, string SQLString)
        {
            using (SqlCommand cmd = new SqlCommand(SQLString, connection))
            {
                try
                {
                    cmd.Connection = connection;
                    cmd.Transaction = trans;
                    int rows = cmd.ExecuteNonQuery();
                    return rows;
                }
                catch (System.Data.SqlClient.SqlException e)
                {
                    trans.Rollback();
                    throw e;
                }
            }
        }

        public static int ExecuteSqlByTime(string SQLString, int Times)
        {
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = new SqlCommand(SQLString, connection))
                {
                    try
                    {
                        connection.Open();
                        cmd.CommandTimeout = Times;
                        int rows = cmd.ExecuteNonQuery();
                        return rows;
                    }
                    catch (System.Data.SqlClient.SqlException e)
                    {
                        connection.Close();
                        throw e;
                    }
                }
            }
        }

        public static int ExecuteSqlByTime(string SQLString, int Times,string conStr)
        {
            using (SqlConnection connection = new SqlConnection(conStr))
            {
                using (SqlCommand cmd = new SqlCommand(SQLString, connection))
                {
                    try
                    {
                        connection.Open();
                        cmd.CommandTimeout = Times;
                        int rows = cmd.ExecuteNonQuery();
                        return rows;
                    }
                    catch (System.Data.SqlClient.SqlException e)
                    {
                        connection.Close();
                        throw e;
                    }
                }
            }
        }

        /// <summary>
        /// 执行Sql和Oracle滴混合事务
        /// </summary>
        /// <param name="list">SQL命令行列表</param>
        /// <param name="oracleCmdSqlList">Oracle命令行列表</param>
        /// <returns>执行结果 0-由于SQL造成事务失败 -1 由于Oracle造成事务失败 1-整体事务执行成功</returns>
        public static int ExecuteSqlTran(List<CommandInfo> list, List<CommandInfo> oracleCmdSqlList)
        {
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                conn.Open();
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = conn;
                SqlTransaction tx = conn.BeginTransaction();
                cmd.Transaction = tx;
                try
                {
                    foreach (CommandInfo myDE in list)
                    {
                        string cmdText = myDE.CommandText;
                        SqlParameter[] cmdParms = (SqlParameter[])myDE.Parameters;
                        PrepareCommand(cmd, conn, tx, cmdText, cmdParms);
                        if (myDE.EffentNextType == EffentNextType.SolicitationEvent)
                        {
                            if (myDE.CommandText.ToLower().IndexOf("count(") == -1)
                            {
                                tx.Rollback();
                                throw new Exception("违背要求" + myDE.CommandText + "必须符合select count(..的格式");
                                //return 0;
                            }

                            object obj = cmd.ExecuteScalar();
                            bool isHave = false;
                            if (obj == null && obj == DBNull.Value)
                            {
                                isHave = false;
                            }
                            isHave = Convert.ToInt32(obj) > 0;
                            if (isHave)
                            {
                                //引发事件
                                myDE.OnSolicitationEvent();
                            }
                        }
                        if (myDE.EffentNextType == EffentNextType.WhenHaveContine || myDE.EffentNextType == EffentNextType.WhenNoHaveContine)
                        {
                            if (myDE.CommandText.ToLower().IndexOf("count(") == -1)
                            {
                                tx.Rollback();
                                throw new Exception("SQL:违背要求" + myDE.CommandText + "必须符合select count(..的格式");
                                //return 0;
                            }

                            object obj = cmd.ExecuteScalar();
                            bool isHave = false;
                            if (obj == null && obj == DBNull.Value)
                            {
                                isHave = false;
                            }
                            isHave = Convert.ToInt32(obj) > 0;

                            if (myDE.EffentNextType == EffentNextType.WhenHaveContine && !isHave)
                            {
                                tx.Rollback();
                                throw new Exception("SQL:违背要求" + myDE.CommandText + "返回值必须大于0");
                                //return 0;
                            }
                            if (myDE.EffentNextType == EffentNextType.WhenNoHaveContine && isHave)
                            {
                                tx.Rollback();
                                throw new Exception("SQL:违背要求" + myDE.CommandText + "返回值必须等于0");
                                //return 0;
                            }
                            continue;
                        }
                        int val = cmd.ExecuteNonQuery();
                        if (myDE.EffentNextType == EffentNextType.ExcuteEffectRows && val == 0)
                        {
                            tx.Rollback();
                            throw new Exception("SQL:违背要求" + myDE.CommandText + "必须有影响行");
                            //return 0;
                        }
                        cmd.Parameters.Clear();
                    }
                    //string oraConnectionString = PubConstant.GetConnectionString("ConnectionStringPPC");
                    //bool res = OracleHelper.ExecuteSqlTran(oraConnectionString, oracleCmdSqlList);
                    //if (!res)
                    //{
                    //    tx.Rollback();
                    //    throw new Exception("Oracle执行失败");
                    // return -1;
                    //}
                    tx.Commit();
                    return 1;
                }
                catch (System.Data.SqlClient.SqlException e)
                {
                    tx.Rollback();
                    throw e;
                }
                catch (Exception e)
                {
                    tx.Rollback();
                    throw e;
                }
            }
        }
        public static int ExecuteSqlTran(List<CommandInfo> list, List<CommandInfo> oracleCmdSqlList,string conStr)
        {
            using (SqlConnection conn = new SqlConnection(conStr))
            {
                conn.Open();
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = conn;
                SqlTransaction tx = conn.BeginTransaction();
                cmd.Transaction = tx;
                try
                {
                    foreach (CommandInfo myDE in list)
                    {
                        string cmdText = myDE.CommandText;
                        SqlParameter[] cmdParms = (SqlParameter[])myDE.Parameters;
                        PrepareCommand(cmd, conn, tx, cmdText, cmdParms);
                        if (myDE.EffentNextType == EffentNextType.SolicitationEvent)
                        {
                            if (myDE.CommandText.ToLower().IndexOf("count(") == -1)
                            {
                                tx.Rollback();
                                throw new Exception("违背要求" + myDE.CommandText + "必须符合select count(..的格式");
                                //return 0;
                            }

                            object obj = cmd.ExecuteScalar();
                            bool isHave = false;
                            if (obj == null && obj == DBNull.Value)
                            {
                                isHave = false;
                            }
                            isHave = Convert.ToInt32(obj) > 0;
                            if (isHave)
                            {
                                //引发事件
                                myDE.OnSolicitationEvent();
                            }
                        }
                        if (myDE.EffentNextType == EffentNextType.WhenHaveContine || myDE.EffentNextType == EffentNextType.WhenNoHaveContine)
                        {
                            if (myDE.CommandText.ToLower().IndexOf("count(") == -1)
                            {
                                tx.Rollback();
                                throw new Exception("SQL:违背要求" + myDE.CommandText + "必须符合select count(..的格式");
                                //return 0;
                            }

                            object obj = cmd.ExecuteScalar();
                            bool isHave = false;
                            if (obj == null && obj == DBNull.Value)
                            {
                                isHave = false;
                            }
                            isHave = Convert.ToInt32(obj) > 0;

                            if (myDE.EffentNextType == EffentNextType.WhenHaveContine && !isHave)
                            {
                                tx.Rollback();
                                throw new Exception("SQL:违背要求" + myDE.CommandText + "返回值必须大于0");
                                //return 0;
                            }
                            if (myDE.EffentNextType == EffentNextType.WhenNoHaveContine && isHave)
                            {
                                tx.Rollback();
                                throw new Exception("SQL:违背要求" + myDE.CommandText + "返回值必须等于0");
                                //return 0;
                            }
                            continue;
                        }
                        int val = cmd.ExecuteNonQuery();
                        if (myDE.EffentNextType == EffentNextType.ExcuteEffectRows && val == 0)
                        {
                            tx.Rollback();
                            throw new Exception("SQL:违背要求" + myDE.CommandText + "必须有影响行");
                            //return 0;
                        }
                        cmd.Parameters.Clear();
                    }
                    //string oraConnectionString = PubConstant.GetConnectionString("ConnectionStringPPC");
                    //bool res = OracleHelper.ExecuteSqlTran(oraConnectionString, oracleCmdSqlList);
                    //if (!res)
                    //{
                    //    tx.Rollback();
                    //    throw new Exception("Oracle执行失败");
                    // return -1;
                    //}
                    tx.Commit();
                    return 1;
                }
                catch (System.Data.SqlClient.SqlException e)
                {
                    tx.Rollback();
                    throw e;
                }
                catch (Exception e)
                {
                    tx.Rollback();
                    throw e;
                }
            }
        }

        /// <summary>
        /// 执行多条SQL语句，实现数据库事务。
        /// </summary>
        /// <param name="SQLStringList">多条SQL语句</param>		
        public static int ExecuteSqlTran(List<String> SQLStringList)
        {
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                conn.Open();
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = conn;
                SqlTransaction tx = conn.BeginTransaction();
                cmd.Transaction = tx;
                try
                {
                    int count = 0;
                    for (int n = 0; n < SQLStringList.Count; n++)
                    {
                        string strsql = SQLStringList[n];
                        if (strsql.Trim().Length > 1)
                        {
                            cmd.CommandText = strsql;
                            count += cmd.ExecuteNonQuery();
                        }
                    }
                    tx.Commit();
                    return count;
                }
                catch
                {
                    tx.Rollback();
                    return 0;
                }
            }
        }
        public static int ExecuteSqlTran(List<String> SQLStringList,string conStr)
        {
            using (SqlConnection conn = new SqlConnection(conStr))
            {
                conn.Open();
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = conn;
                SqlTransaction tx = conn.BeginTransaction();
                cmd.Transaction = tx;
                try
                {
                    int count = 0;
                    for (int n = 0; n < SQLStringList.Count; n++)
                    {
                        string strsql = SQLStringList[n];
                        if (strsql.Trim().Length > 1)
                        {
                            cmd.CommandText = strsql;
                            count += cmd.ExecuteNonQuery();
                        }
                    }
                    tx.Commit();
                    return count;
                }
                catch
                {
                    tx.Rollback();
                    return 0;
                }
            }
        }
        public static int ExecuteSql(string SQLString, string content,string conStr)
        {
            using (SqlConnection connection = new SqlConnection(conStr))
            {
                SqlCommand cmd = new SqlCommand(SQLString, connection);
                System.Data.SqlClient.SqlParameter myParameter = new System.Data.SqlClient.SqlParameter("@content", SqlDbType.NText);
                myParameter.Value = content;
                cmd.Parameters.Add(myParameter);
                try
                {
                    connection.Open();
                    int rows = cmd.ExecuteNonQuery();
                    return rows;
                }
                catch (System.Data.SqlClient.SqlException e)
                {
                    throw e;
                }
                finally
                {
                    cmd.Dispose();
                    connection.Close();
                }
            }
        }
        /// <summary>
        /// 执行带一个存储过程参数的的SQL语句。
        /// </summary>
        /// <param name="SQLString">SQL语句</param>
        /// <param name="content">参数内容,比如一个字段是格式复杂的文章，有特殊符号，可以通过这个方式添加</param>
        /// <returns>影响的记录数</returns>
        public static object ExecuteSqlGet(string SQLString, string content)
        {
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                SqlCommand cmd = new SqlCommand(SQLString, connection);
                System.Data.SqlClient.SqlParameter myParameter = new System.Data.SqlClient.SqlParameter("@content", SqlDbType.NText);
                myParameter.Value = content;
                cmd.Parameters.Add(myParameter);
                try
                {
                    connection.Open();
                    object obj = cmd.ExecuteScalar();
                    if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
                    {
                        return null;
                    }
                    else
                    {
                        return obj;
                    }
                }
                catch (System.Data.SqlClient.SqlException e)
                {
                    throw e;
                }
                finally
                {
                    cmd.Dispose();
                    connection.Close();
                }
            }
        }
        public static object ExecuteSqlGet(string SQLString, string content,string conStr)
        {
            using (SqlConnection connection = new SqlConnection(conStr))
            {
                SqlCommand cmd = new SqlCommand(SQLString, connection);
                System.Data.SqlClient.SqlParameter myParameter = new System.Data.SqlClient.SqlParameter("@content", SqlDbType.NText);
                myParameter.Value = content;
                cmd.Parameters.Add(myParameter);
                try
                {
                    connection.Open();
                    object obj = cmd.ExecuteScalar();
                    if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
                    {
                        return null;
                    }
                    else
                    {
                        return obj;
                    }
                }
                catch (System.Data.SqlClient.SqlException e)
                {
                    throw e;
                }
                finally
                {
                    cmd.Dispose();
                    connection.Close();
                }
            }
        }
        /// <summary>
        /// 向数据库里插入图像格式的字段(和上面情况类似的另一种实例)
        /// </summary>
        /// <param name="strSQL">SQL语句</param>
        /// <param name="fs">图像字节,数据库的字段类型为image的情况</param>
        /// <returns>影响的记录数</returns>
        public static int ExecuteSqlInsertImg(string strSQL, byte[] fs)
        {
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                SqlCommand cmd = new SqlCommand(strSQL, connection);
                System.Data.SqlClient.SqlParameter myParameter = new System.Data.SqlClient.SqlParameter("@fs", SqlDbType.Image);
                myParameter.Value = fs;
                cmd.Parameters.Add(myParameter);
                try
                {
                    connection.Open();
                    int rows = cmd.ExecuteNonQuery();
                    return rows;
                }
                catch (System.Data.SqlClient.SqlException e)
                {
                    throw e;
                }
                finally
                {
                    cmd.Dispose();
                    connection.Close();
                }
            }
        }
        public static int ExecuteSqlInsertImg(string strSQL, byte[] fs,string conStr)
        {
            using (SqlConnection connection = new SqlConnection(conStr))
            {
                SqlCommand cmd = new SqlCommand(strSQL, connection);
                System.Data.SqlClient.SqlParameter myParameter = new System.Data.SqlClient.SqlParameter("@fs", SqlDbType.Image);
                myParameter.Value = fs;
                cmd.Parameters.Add(myParameter);
                try
                {
                    connection.Open();
                    int rows = cmd.ExecuteNonQuery();
                    return rows;
                }
                catch (System.Data.SqlClient.SqlException e)
                {
                    throw e;
                }
                finally
                {
                    cmd.Dispose();
                    connection.Close();
                }
            }
        }
        /// <summary>
        /// 执行一条计算查询结果语句，返回查询结果（object）。
        /// </summary>
        /// <param name="SQLString">计算查询结果语句</param>
        /// <returns>查询结果（object）</returns>
        public static object GetSingle(string SQLString)
        {
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = new SqlCommand(SQLString, connection))
                {
                    try
                    {
                        connection.Open();
                        object obj = cmd.ExecuteScalar();
                        if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
                        {
                            return null;
                        }
                        else
                        {
                            return obj;
                        }
                    }
                    catch (System.Data.SqlClient.SqlException e)
                    {
                        connection.Close();
                        throw e;
                    }
                }
            }
        }
        public static object GetSingle(string SQLString,string conStr)
        {
            using (SqlConnection connection = new SqlConnection(conStr))
            {
                using (SqlCommand cmd = new SqlCommand(SQLString, connection))
                {
                    try
                    {
                        connection.Open();
                        object obj = cmd.ExecuteScalar();
                        if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
                        {
                            return null;
                        }
                        else
                        {
                            return obj;
                        }
                    }
                    catch (System.Data.SqlClient.SqlException e)
                    {
                        connection.Close();
                        throw e;
                    }
                }
            }
        }
        public static object GetSingle(string SQLString, int Times)
        {
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = new SqlCommand(SQLString, connection))
                {
                    try
                    {
                        connection.Open();
                        cmd.CommandTimeout = Times;
                        object obj = cmd.ExecuteScalar();
                        if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
                        {
                            return null;
                        }
                        else
                        {
                            return obj;
                        }
                    }
                    catch (System.Data.SqlClient.SqlException e)
                    {
                        connection.Close();
                        throw e;
                    }
                }
            }
        }
        public static object GetSingle(string SQLString, int Times,string conStr)
        {
            using (SqlConnection connection = new SqlConnection(conStr))
            {
                using (SqlCommand cmd = new SqlCommand(SQLString, connection))
                {
                    try
                    {
                        connection.Open();
                        cmd.CommandTimeout = Times;
                        object obj = cmd.ExecuteScalar();
                        if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
                        {
                            return null;
                        }
                        else
                        {
                            return obj;
                        }
                    }
                    catch (System.Data.SqlClient.SqlException e)
                    {
                        connection.Close();
                        throw e;
                    }
                }
            }
        }
        /// <summary>
        /// 执行查询语句，返回SqlDataReader ( 注意：调用该方法后，一定要对SqlDataReader进行Close )
        /// </summary>
        /// <param name="strSQL">查询语句</param>
        /// <returns>SqlDataReader</returns>
        public static SqlDataReader ExecuteReader(string strSQL)
        {
            SqlConnection connection = new SqlConnection(connectionString);
            SqlCommand cmd = new SqlCommand(strSQL, connection);
            try
            {
                connection.Open();
                SqlDataReader myReader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                return myReader;
            }
            catch (System.Data.SqlClient.SqlException e)
            {
                throw e;
            }

        }
        public static SqlDataReader ExecuteReader(string strSQL,string conStr)
        {
            SqlConnection connection = new SqlConnection(conStr);
            SqlCommand cmd = new SqlCommand(strSQL, connection);
            try
            {
                connection.Open();
                SqlDataReader myReader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                return myReader;
            }
            catch (System.Data.SqlClient.SqlException e)
            {
                throw e;
            }

        }
        /// <summary>
        /// 执行查询语句，返回DataSet
        /// </summary>
        /// <param name="SQLString">查询语句</param>
        /// <returns>DataSet</returns>
        public static DataSet QueryErp1(string Conn,string SQLString, ref string error)
        {
            if (string.IsNullOrEmpty(SQLString.Trim())) return null;
            try
            {
                using (SqlConnection connection = new SqlConnection(Conn))
                {
                    if (connection.State != ConnectionState.Open)
                        connection.Open();
                    //开始事物
                    using (SqlTransaction trans = connection.BeginTransaction())
                    {
                        using (SqlCommand cmd = new SqlCommand())
                        {
                            PrepareCommand(cmd, connection, trans, SQLString, null);
                            using (SqlDataAdapter da = new SqlDataAdapter(cmd))
                            {
                                DataSet ds = new DataSet();
                                try
                                {
                                    da.Fill(ds, "ds");
                                    cmd.Parameters.Clear();
                                    trans.Commit();
                                }
                                catch (System.Data.SqlClient.SqlException ex)
                                {
                                    trans.Rollback();
                                    error = ex.ToString();
                                }
                                return ds;
                            }
                        }
                    }
                }
            }
            catch (System.Data.SqlClient.SqlException ex)
            {
                error = ex.ToString();
            }
            catch (Exception ex)
            {
                error = ex.ToString();
            }
            return new DataSet();
        }
        
        /// <summary>
        /// 执行查询语句，返回DataSet
        /// </summary>
        /// <param name="SQLString">查询语句</param>
        /// <returns>DataSet</returns>
        public static DataSet QueryErp(string SQLString, ref string error)
        {
            if (string.IsNullOrEmpty(SQLString.Trim())) return null;
            try
            {
                using (SqlConnection connection = new SqlConnection(connectionString))
                {
                    if (connection.State != ConnectionState.Open)
                        connection.Open();
                    //开始事物
                    using (SqlTransaction trans = connection.BeginTransaction())
                    {
                        using (SqlCommand cmd = new SqlCommand())
                        {
                            PrepareCommand(cmd, connection, trans, SQLString, null);
                            using (SqlDataAdapter da = new SqlDataAdapter(cmd))
                            {
                                DataSet ds = new DataSet();
                                try
                                {
                                    da.Fill(ds, "ds");
                                    cmd.Parameters.Clear();
                                    trans.Commit();
                                }
                                catch (System.Data.SqlClient.SqlException ex)
                                {
                                    trans.Rollback();
                                    error = ex.ToString();
                                }
                                return ds;
                            }
                        }
                    }
                }
            }
            catch (System.Data.SqlClient.SqlException ex)
            {
                error = ex.ToString();
            }
            catch (Exception ex)
            {
                error = ex.ToString();
            }
            return new DataSet();
        }
        public static DataSet QueryErp(string SQLString,string conStr, ref string error)
        {
            if (string.IsNullOrEmpty(SQLString.Trim())) return null;
            try
            {
                using (SqlConnection connection = new SqlConnection(conStr))
                {
                    if (connection.State != ConnectionState.Open)
                        connection.Open();
                    //开始事物
                    using (SqlTransaction trans = connection.BeginTransaction())
                    {
                        using (SqlCommand cmd = new SqlCommand())
                        {
                            PrepareCommand(cmd, connection, trans, SQLString, null);
                            using (SqlDataAdapter da = new SqlDataAdapter(cmd))
                            {
                                DataSet ds = new DataSet();
                                try
                                {
                                    da.Fill(ds, "ds");
                                    cmd.Parameters.Clear();
                                    trans.Commit();
                                }
                                catch (System.Data.SqlClient.SqlException ex)
                                {
                                    trans.Rollback();
                                    error = ex.ToString();
                                }
                                return ds;
                            }
                        }
                    }
                }
            }
            catch (System.Data.SqlClient.SqlException ex)
            {
                error = ex.ToString();
            }
            catch (Exception ex)
            {
                error = ex.ToString();
            }
            return new DataSet();
        }
        /// <summary>
        /// 执行查询语句，返回DataSet
        /// </summary>
        /// <param name="SQLString">查询语句</param>
        /// <returns>DataSet</returns>
        public static DataSet Query(string SQLString)
        {
            SQLString = EscapeBusinessString(SQLString);
            if (string.IsNullOrEmpty(SQLString.Trim())) return null;
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                DataSet ds = new DataSet();
                try
                {
                    connection.Open();
                    SqlDataAdapter command = new SqlDataAdapter(SQLString, connection);
                    command.Fill(ds, "ds");
                }
                catch (System.Data.SqlClient.SqlException ex)
                {
                    //此处要注释掉
                    //Log.SaveNote("DBHelper.Query报错，SQLTEXT：【" + SQLString + "】错误是：" + ex.Message);
                    //error = ex.Message;
                }
                return ds;
            }

        }
        public static DataSet Query(string SQLString,string conStr)
        {
            SQLString = EscapeBusinessString(SQLString);
            if (string.IsNullOrEmpty(SQLString.Trim())) return null;
            using (SqlConnection connection = new SqlConnection(conStr))
            {
                DataSet ds = new DataSet();
                try
                {
                    connection.Open();
                    SqlDataAdapter command = new SqlDataAdapter(SQLString, connection);
                    command.Fill(ds, "ds");
                }
                catch (System.Data.SqlClient.SqlException ex)
                {
                    //此处要注释掉
                    //Log.SaveNote("DBHelper.Query报错，SQLTEXT：【" + SQLString + "】错误是：" + ex.Message);
                    //error = ex.Message;
                }
                return ds;
            }

        }
        public static DataSet Query(string SQLString, int Times)
        {
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                DataSet ds = new DataSet();
                try
                {
                    connection.Open();
                    SqlDataAdapter command = new SqlDataAdapter(SQLString, connection);
                    command.SelectCommand.CommandTimeout = Times;
                    command.Fill(ds, "ds");
                }
                catch (System.Data.SqlClient.SqlException ex)
                {
                    throw new Exception(ex.Message);
                }
                return ds;
            }
        }

        public static DataSet Query(string SQLString, int Times,string conStr)
        {
            using (SqlConnection connection = new SqlConnection(conStr))
            {
                DataSet ds = new DataSet();
                try
                {
                    connection.Open();
                    SqlDataAdapter command = new SqlDataAdapter(SQLString, connection);
                    command.SelectCommand.CommandTimeout = Times;
                    command.Fill(ds, "ds");
                }
                catch (System.Data.SqlClient.SqlException ex)
                {
                    throw new Exception(ex.Message);
                }
                return ds;
            }
        }


        /// <summary>
        /// 2012-2-21新增重载，执行查询语句，返回DataSet
        /// </summary>
        /// <param name="connection">SqlConnection对象</param>
        /// <param name="trans">SqlTransaction事务</param>
        /// <param name="SQLString">SQL语句</param>
        /// <returns>DataSet</returns>
        public static DataSet Query(SqlConnection connection, SqlTransaction trans, string SQLString)
        {
            DataSet ds = new DataSet();
            try
            {
                SqlDataAdapter command = new SqlDataAdapter(SQLString, connection);
                command.SelectCommand.Transaction = trans;
                command.Fill(ds, "ds");
            }
            catch (System.Data.SqlClient.SqlException ex)
            {
                throw new Exception(ex.Message);
            }
            return ds;

        }


        #endregion

        #region 执行带参数的SQL语句

        /// <summary>
        /// 执行多条SQL语句，实现数据库事务。
        /// </summary>
        /// <param name="SQLStringList">SQL语句的（key为sql语句，value是该语句的List<string[]>）</param>
        public static string exec(string sqltext, List<string[]> cmdList)
        {
            string returnStr = "";
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                conn.Open();
                using (SqlTransaction trans = conn.BeginTransaction())
                {
                    using (SqlCommand cmd = new SqlCommand())
                    {
                        try
                        {
                            PrepareCommand(cmd, conn, trans, sqltext, getparms(cmdList));

                            object obj = cmd.ExecuteScalar();
                            if (obj == null && obj == DBNull.Value)
                            {
                                //不能。没有返回值
                                returnStr = "-999999";
                                trans.Rollback();
                            }
                            else
                            {
                                //返回sql事务中的数据
                                returnStr = Convert.ToString(obj);
                            }
                            cmd.Parameters.Clear();

                            trans.Commit();
                        }
                        catch (Exception ex)
                        {
                            returnStr = "-999999" + ex.Message;
                            trans.Rollback();
                            //throw;
                        }
                    }
                }
            }
            return returnStr;
        }
        public static string exec(string sqltext, List<string[]> cmdList,string conStr)
        {
            string returnStr = "";
            using (SqlConnection conn = new SqlConnection(conStr))
            {
                conn.Open();
                using (SqlTransaction trans = conn.BeginTransaction())
                {
                    using (SqlCommand cmd = new SqlCommand())
                    {
                        try
                        {
                            PrepareCommand(cmd, conn, trans, sqltext, getparms(cmdList));

                            object obj = cmd.ExecuteScalar();
                            if (obj == null && obj == DBNull.Value)
                            {
                                //不能。没有返回值
                                returnStr = "-999999";
                                trans.Rollback();
                            }
                            else
                            {
                                //返回sql事务中的数据
                                returnStr = Convert.ToString(obj);
                            }
                            cmd.Parameters.Clear();

                            trans.Commit();
                        }
                        catch (Exception ex)
                        {
                            returnStr = "-999999" + ex.Message;
                            trans.Rollback();
                            //throw;
                        }
                    }
                }
            }
            return returnStr;
        }
        /// <summary>
        /// 执行多条SQL语句，实现数据库事务。
        /// </summary>
        /// <param name="SQLStringList">SQL语句的（key为sql语句，value是该语句的List<string[]>）</param>
        public static void exec(ref string msg, Dictionary<string, List<string[]>> cmdList)
        {
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                conn.Open();
                using (SqlTransaction trans = conn.BeginTransaction())
                {
                    using (SqlCommand cmd = new SqlCommand())
                    {
                        try
                        {
                            //循环
                            foreach (var myDE in cmdList)
                            {
                                string cmdText = myDE.Key;
                                PrepareCommand(cmd, conn, trans, cmdText, getparms(myDE.Value));

                                object obj = cmd.ExecuteScalar();
                                if (obj == null && obj == DBNull.Value)
                                {
                                    //其中一条事务没有返回正确的状态码。
                                    msg = "-999999";
                                    trans.Rollback();
                                    break;
                                }
                                int isHave = Convert.ToInt32(obj);
                                //当前事务表示不继续往下执行
                                if (isHave < 0)
                                {
                                    //小于0的全部是不继续往下执行
                                    msg = isHave.ToString();
                                    trans.Rollback();
                                    break;
                                }
                                cmd.Parameters.Clear();
                            }
                            trans.Commit();
                        }
                        catch (Exception ex)
                        {
                            msg = ex.Message;
                            trans.Rollback();
                            //throw;
                        }
                    }
                }
            }
        }
        public static void exec(ref string msg, Dictionary<string, List<string[]>> cmdList,string conStr)
        {
            using (SqlConnection conn = new SqlConnection(conStr))
            {
                conn.Open();
                using (SqlTransaction trans = conn.BeginTransaction())
                {
                    using (SqlCommand cmd = new SqlCommand())
                    {
                        try
                        {
                            //循环
                            foreach (var myDE in cmdList)
                            {
                                string cmdText = myDE.Key;
                                PrepareCommand(cmd, conn, trans, cmdText, getparms(myDE.Value));

                                object obj = cmd.ExecuteScalar();
                                if (obj == null && obj == DBNull.Value)
                                {
                                    //其中一条事务没有返回正确的状态码。
                                    msg = "-999999";
                                    trans.Rollback();
                                    break;
                                }
                                int isHave = Convert.ToInt32(obj);
                                //当前事务表示不继续往下执行
                                if (isHave < 0)
                                {
                                    //小于0的全部是不继续往下执行
                                    msg = isHave.ToString();
                                    trans.Rollback();
                                    break;
                                }
                                cmd.Parameters.Clear();
                            }
                            trans.Commit();
                        }
                        catch (Exception ex)
                        {
                            msg = ex.Message;
                            trans.Rollback();
                            //throw;
                        }
                    }
                }
            }
        }
        /// <summary>
        /// 执行SQL语句，返回影响的记录数
        /// </summary>
        /// <param name="SQLString">SQL语句</param>
        /// <returns>影响的记录数</returns>
        public static int ExecuteSql(string SQLString, params SqlParameter[] cmdParms)
        {
            SQLString = EscapeBusinessString(SQLString);
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = new SqlCommand())
                {
                    try
                    {
                        PrepareCommand(cmd, connection, null, SQLString, cmdParms);
                        int rows = cmd.ExecuteNonQuery();
                        cmd.Parameters.Clear();
                        return rows;
                    }
                    catch (System.Data.SqlClient.SqlException e)
                    {
                        throw e;
                    }
                }
            }
        }

        public static int ExecuteSql(string SQLString,string conStr, params SqlParameter[] cmdParms)
        {
            SQLString = EscapeBusinessString(SQLString);
            using (SqlConnection connection = new SqlConnection(conStr))
            {
                using (SqlCommand cmd = new SqlCommand())
                {
                    try
                    {
                        PrepareCommand(cmd, connection, null, SQLString, cmdParms);
                        int rows = cmd.ExecuteNonQuery();
                        cmd.Parameters.Clear();
                        return rows;
                    }
                    catch (System.Data.SqlClient.SqlException e)
                    {
                        throw e;
                    }
                }
            }
        }

        /// <summary>
        /// 2012-2-29新增重载，执行SQL语句，返回影响的记录数
        /// </summary>
        /// <param name="connection">SqlConnection对象</param>
        /// <param name="trans">SqlTransaction对象</param>
        /// <param name="SQLString">SQL语句</param>
        /// <returns>影响的记录数</returns>
        public static int ExecuteSql(SqlConnection connection, SqlTransaction trans, string SQLString, params SqlParameter[] cmdParms)
        {
            using (SqlCommand cmd = new SqlCommand())
            {
                try
                {
                    PrepareCommand(cmd, connection, trans, SQLString, cmdParms);
                    int rows = cmd.ExecuteNonQuery();
                    cmd.Parameters.Clear();
                    return rows;
                }
                catch (System.Data.SqlClient.SqlException e)
                {
                    trans.Rollback();
                    throw e;
                }
            }
        }

        /// <summary>
        /// 执行多条SQL语句，实现数据库事务。
        /// </summary>
        /// <param name="SQLStringList">SQL语句的哈希表（key为sql语句，value是该语句的SqlParameter[]）</param>
        public static void ExecuteSqlTran(Hashtable SQLStringList)
        {
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                conn.Open();
                using (SqlTransaction trans = conn.BeginTransaction())
                {
                    using (SqlCommand cmd = new SqlCommand())
                    {
                        try
                        {
                            //循环
                            foreach (DictionaryEntry myDE in SQLStringList)
                            {
                                string cmdText = myDE.Key.ToString();
                                SqlParameter[] cmdParms = (SqlParameter[])myDE.Value;
                                PrepareCommand(cmd, conn, trans, cmdText, cmdParms);
                                int val = cmd.ExecuteNonQuery();
                                cmd.Parameters.Clear();
                            }
                            trans.Commit();
                        }
                        catch
                        {
                            trans.Rollback();
                            throw;
                        }
                    }
                }
            }
        }
        public static void ExecuteSqlTran(Hashtable SQLStringList,string conStr)
        {
            using (SqlConnection conn = new SqlConnection(conStr))
            {
                conn.Open();
                using (SqlTransaction trans = conn.BeginTransaction())
                {
                    using (SqlCommand cmd = new SqlCommand())
                    {
                        try
                        {
                            //循环
                            foreach (DictionaryEntry myDE in SQLStringList)
                            {
                                string cmdText = myDE.Key.ToString();
                                SqlParameter[] cmdParms = (SqlParameter[])myDE.Value;
                                PrepareCommand(cmd, conn, trans, cmdText, cmdParms);
                                int val = cmd.ExecuteNonQuery();
                                cmd.Parameters.Clear();
                            }
                            trans.Commit();
                        }
                        catch
                        {
                            trans.Rollback();
                            throw;
                        }
                    }
                }
            }
        }
        /// <summary>
        /// 执行多条SQL语句，实现数据库事务。
        /// </summary>
        /// <param name="SQLStringList">SQL语句的哈希表（key为sql语句，value是该语句的SqlParameter[]）</param>
        public static int ExecuteSqlTran(System.Collections.Generic.List<CommandInfo> cmdList)
        {
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                conn.Open();
                using (SqlTransaction trans = conn.BeginTransaction())
                {
                    SqlCommand cmd = new SqlCommand();
                    try
                    {
                        int count = 0;
                        //循环
                        foreach (CommandInfo myDE in cmdList)
                        {
                            string cmdText = myDE.CommandText;
                            SqlParameter[] cmdParms = (SqlParameter[])myDE.Parameters;
                            PrepareCommand(cmd, conn, trans, cmdText, cmdParms);

                            if (myDE.EffentNextType == EffentNextType.WhenHaveContine || myDE.EffentNextType == EffentNextType.WhenNoHaveContine)
                            {
                                if (myDE.CommandText.ToLower().IndexOf("count(") == -1)
                                {
                                    trans.Rollback();
                                    return 0;
                                }

                                object obj = cmd.ExecuteScalar();
                                bool isHave = false;
                                if (obj == null && obj == DBNull.Value)
                                {
                                    isHave = false;
                                }
                                isHave = Convert.ToInt32(obj) > 0;

                                if (myDE.EffentNextType == EffentNextType.WhenHaveContine && !isHave)
                                {
                                    trans.Rollback();
                                    return 0;
                                }
                                if (myDE.EffentNextType == EffentNextType.WhenNoHaveContine && isHave)
                                {
                                    trans.Rollback();
                                    return 0;
                                }
                                continue;
                            }
                            int val = cmd.ExecuteNonQuery();
                            count += val;
                            if (myDE.EffentNextType == EffentNextType.ExcuteEffectRows && val == 0)
                            {
                                trans.Rollback();
                                return 0;
                            }
                            cmd.Parameters.Clear();
                        }
                        trans.Commit();
                        return count;
                    }
                    catch
                    {
                        trans.Rollback();
                        throw;
                    }
                }
            }
        }
        public static int ExecuteSqlTran(System.Collections.Generic.List<CommandInfo> cmdList,string conStr)
        {
            using (SqlConnection conn = new SqlConnection(conStr))
            {
                conn.Open();
                using (SqlTransaction trans = conn.BeginTransaction())
                {
                    SqlCommand cmd = new SqlCommand();
                    try
                    {
                        int count = 0;
                        //循环
                        foreach (CommandInfo myDE in cmdList)
                        {
                            string cmdText = myDE.CommandText;
                            SqlParameter[] cmdParms = (SqlParameter[])myDE.Parameters;
                            PrepareCommand(cmd, conn, trans, cmdText, cmdParms);

                            if (myDE.EffentNextType == EffentNextType.WhenHaveContine || myDE.EffentNextType == EffentNextType.WhenNoHaveContine)
                            {
                                if (myDE.CommandText.ToLower().IndexOf("count(") == -1)
                                {
                                    trans.Rollback();
                                    return 0;
                                }

                                object obj = cmd.ExecuteScalar();
                                bool isHave = false;
                                if (obj == null && obj == DBNull.Value)
                                {
                                    isHave = false;
                                }
                                isHave = Convert.ToInt32(obj) > 0;

                                if (myDE.EffentNextType == EffentNextType.WhenHaveContine && !isHave)
                                {
                                    trans.Rollback();
                                    return 0;
                                }
                                if (myDE.EffentNextType == EffentNextType.WhenNoHaveContine && isHave)
                                {
                                    trans.Rollback();
                                    return 0;
                                }
                                continue;
                            }
                            int val = cmd.ExecuteNonQuery();
                            count += val;
                            if (myDE.EffentNextType == EffentNextType.ExcuteEffectRows && val == 0)
                            {
                                trans.Rollback();
                                return 0;
                            }
                            cmd.Parameters.Clear();
                        }
                        trans.Commit();
                        return count;
                    }
                    catch
                    {
                        trans.Rollback();
                        throw;
                    }
                }
            }
        }
        /// <summary>
        /// 执行多条SQL语句，实现数据库事务。
        /// </summary>
        /// <param name="SQLStringList">SQL语句的哈希表（key为sql语句，value是该语句的SqlParameter[]）</param>
        public static void ExecuteSqlTranWithIndentity(System.Collections.Generic.List<CommandInfo> SQLStringList)
        {
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                conn.Open();
                using (SqlTransaction trans = conn.BeginTransaction())
                {
                    SqlCommand cmd = new SqlCommand();
                    try
                    {
                        int indentity = 0;
                        //循环
                        foreach (CommandInfo myDE in SQLStringList)
                        {
                            string cmdText = myDE.CommandText;
                            SqlParameter[] cmdParms = (SqlParameter[])myDE.Parameters;
                            foreach (SqlParameter q in cmdParms)
                            {
                                if (q.Direction == ParameterDirection.InputOutput)
                                {
                                    q.Value = indentity;
                                }
                            }
                            PrepareCommand(cmd, conn, trans, cmdText, cmdParms);
                            int val = cmd.ExecuteNonQuery();
                            foreach (SqlParameter q in cmdParms)
                            {
                                if (q.Direction == ParameterDirection.Output)
                                {
                                    indentity = Convert.ToInt32(q.Value);
                                }
                            }
                            cmd.Parameters.Clear();
                        }
                        trans.Commit();
                    }
                    catch
                    {
                        trans.Rollback();
                        throw;
                    }
                }
            }
        }
        public static void ExecuteSqlTranWithIndentity(System.Collections.Generic.List<CommandInfo> SQLStringList,string conStr)
        {
            using (SqlConnection conn = new SqlConnection(conStr))
            {
                conn.Open();
                using (SqlTransaction trans = conn.BeginTransaction())
                {
                    SqlCommand cmd = new SqlCommand();
                    try
                    {
                        int indentity = 0;
                        //循环
                        foreach (CommandInfo myDE in SQLStringList)
                        {
                            string cmdText = myDE.CommandText;
                            SqlParameter[] cmdParms = (SqlParameter[])myDE.Parameters;
                            foreach (SqlParameter q in cmdParms)
                            {
                                if (q.Direction == ParameterDirection.InputOutput)
                                {
                                    q.Value = indentity;
                                }
                            }
                            PrepareCommand(cmd, conn, trans, cmdText, cmdParms);
                            int val = cmd.ExecuteNonQuery();
                            foreach (SqlParameter q in cmdParms)
                            {
                                if (q.Direction == ParameterDirection.Output)
                                {
                                    indentity = Convert.ToInt32(q.Value);
                                }
                            }
                            cmd.Parameters.Clear();
                        }
                        trans.Commit();
                    }
                    catch
                    {
                        trans.Rollback();
                        throw;
                    }
                }
            }
        }
        /// <summary>
        /// 执行多条SQL语句，实现数据库事务。
        /// </summary>
        /// <param name="SQLStringList">SQL语句的哈希表（key为sql语句，value是该语句的SqlParameter[]）</param>
        public static void ExecuteSqlTranWithIndentity(Hashtable SQLStringList)
        {
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                conn.Open();
                using (SqlTransaction trans = conn.BeginTransaction())
                {
                    SqlCommand cmd = new SqlCommand();
                    try
                    {
                        int indentity = 0;
                        //循环
                        foreach (DictionaryEntry myDE in SQLStringList)
                        {
                            string cmdText = myDE.Key.ToString();
                            SqlParameter[] cmdParms = (SqlParameter[])myDE.Value;
                            foreach (SqlParameter q in cmdParms)
                            {
                                if (q.Direction == ParameterDirection.InputOutput)
                                {
                                    q.Value = indentity;
                                }
                            }
                            PrepareCommand(cmd, conn, trans, cmdText, cmdParms);
                            int val = cmd.ExecuteNonQuery();
                            foreach (SqlParameter q in cmdParms)
                            {
                                if (q.Direction == ParameterDirection.Output)
                                {
                                    indentity = Convert.ToInt32(q.Value);
                                }
                            }
                            cmd.Parameters.Clear();
                        }
                        trans.Commit();
                    }
                    catch
                    {
                        trans.Rollback();
                        throw;
                    }
                }
            }
        }
        public static void ExecuteSqlTranWithIndentity(Hashtable SQLStringList,string conStr)
        {
            using (SqlConnection conn = new SqlConnection(conStr))
            {
                conn.Open();
                using (SqlTransaction trans = conn.BeginTransaction())
                {
                    SqlCommand cmd = new SqlCommand();
                    try
                    {
                        int indentity = 0;
                        //循环
                        foreach (DictionaryEntry myDE in SQLStringList)
                        {
                            string cmdText = myDE.Key.ToString();
                            SqlParameter[] cmdParms = (SqlParameter[])myDE.Value;
                            foreach (SqlParameter q in cmdParms)
                            {
                                if (q.Direction == ParameterDirection.InputOutput)
                                {
                                    q.Value = indentity;
                                }
                            }
                            PrepareCommand(cmd, conn, trans, cmdText, cmdParms);
                            int val = cmd.ExecuteNonQuery();
                            foreach (SqlParameter q in cmdParms)
                            {
                                if (q.Direction == ParameterDirection.Output)
                                {
                                    indentity = Convert.ToInt32(q.Value);
                                }
                            }
                            cmd.Parameters.Clear();
                        }
                        trans.Commit();
                    }
                    catch
                    {
                        trans.Rollback();
                        throw;
                    }
                }
            }
        }
        /// <summary>
        /// 执行一条计算查询结果语句，返回查询结果（object）。
        /// </summary>
        /// <param name="SQLString">计算查询结果语句</param>
        /// <returns>查询结果（object）</returns>
        public static object GetSingle(string SQLString, params SqlParameter[] cmdParms)
        {
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = new SqlCommand())
                {
                    try
                    {
                        PrepareCommand(cmd, connection, null, SQLString, cmdParms);
                        object obj = cmd.ExecuteScalar();
                        cmd.Parameters.Clear();
                        if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
                        {
                            return null;
                        }
                        else
                        {
                            return obj;
                        }
                    }
                    catch (System.Data.SqlClient.SqlException e)
                    {
                        throw e;
                    }
                }
            }
        }
        public static object GetSingle(string SQLString,string conStr, params SqlParameter[] cmdParms)
        {
            using (SqlConnection connection = new SqlConnection(conStr))
            {
                using (SqlCommand cmd = new SqlCommand())
                {
                    try
                    {
                        PrepareCommand(cmd, connection, null, SQLString, cmdParms);
                        object obj = cmd.ExecuteScalar();
                        cmd.Parameters.Clear();
                        if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
                        {
                            return null;
                        }
                        else
                        {
                            return obj;
                        }
                    }
                    catch (System.Data.SqlClient.SqlException e)
                    {
                        throw e;
                    }
                }
            }
        }

        /// <summary>
        /// 2012-2-21新增重载，执行一条计算查询结果语句，返回查询结果（object）。
        /// </summary>
        /// <param name="connection">SqlConnection对象</param>
        /// <param name="trans">SqlTransaction事务</param>
        /// <param name="SQLString">计算查询结果语句</param>
        /// <returns>查询结果（object）</returns>
        public static object GetSingle(SqlConnection connection, SqlTransaction trans, string SQLString, params SqlParameter[] cmdParms)
        {
            using (SqlCommand cmd = new SqlCommand())
            {
                try
                {
                    PrepareCommand(cmd, connection, trans, SQLString, cmdParms);
                    object obj = cmd.ExecuteScalar();
                    cmd.Parameters.Clear();
                    if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
                    {
                        return null;
                    }
                    else
                    {
                        return obj;
                    }
                }
                catch (System.Data.SqlClient.SqlException e)
                {
                    trans.Rollback();
                    throw e;
                }
            }
        }

        /// <summary>
        /// 执行查询语句，返回SqlDataReader ( 注意：调用该方法后，一定要对SqlDataReader进行Close )
        /// </summary>
        /// <param name="strSQL">查询语句</param>
        /// <returns>SqlDataReader</returns>
        public static SqlDataReader ExecuteReader(string SQLString, params SqlParameter[] cmdParms)
        {
            SqlConnection connection = new SqlConnection(connectionString);
            SqlCommand cmd = new SqlCommand();
            try
            {
                PrepareCommand(cmd, connection, null, SQLString, cmdParms);
                SqlDataReader myReader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                cmd.Parameters.Clear();
                return myReader;
            }
            catch (System.Data.SqlClient.SqlException e)
            {
                throw e;
            }
            //			finally
            //			{
            //				cmd.Dispose();
            //				connection.Close();
            //			}	

        }
        public static SqlDataReader ExecuteReader(string SQLString,string conStr, params SqlParameter[] cmdParms)
        {
            SqlConnection connection = new SqlConnection(conStr);
            SqlCommand cmd = new SqlCommand();
            try
            {
                PrepareCommand(cmd, connection, null, SQLString, cmdParms);
                SqlDataReader myReader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                cmd.Parameters.Clear();
                return myReader;
            }
            catch (System.Data.SqlClient.SqlException e)
            {
                throw e;
            }
            //			finally
            //			{
            //				cmd.Dispose();
            //				connection.Close();
            //			}	

        }

        /// <summary>
        /// 执行查询语句，返回DataSet
        /// </summary>
        /// <param name="SQLString">查询语句</param>
        /// <returns>DataSet</returns>
        public static DataSet QueryErp1(string Conn,string SQLString, ref string error, params SqlParameter[] cmdParms)
        {
            if (cmdParms == null) return QueryErp1(Conn,SQLString, ref error);

            if (string.IsNullOrEmpty(SQLString)) return null;
            using (SqlConnection connection = new SqlConnection(Conn))
            {
                if (connection.State != ConnectionState.Open)
                    connection.Open();
                //开始事物
                using (SqlTransaction trans = connection.BeginTransaction())
                {
                    using (SqlCommand cmd = new SqlCommand())
                    {
                        PrepareCommand(cmd, connection, trans, SQLString, cmdParms);
                        using (SqlDataAdapter da = new SqlDataAdapter(cmd))
                        {
                            DataSet ds = new DataSet();
                            try
                            {
                                da.Fill(ds, "ds");
                                cmd.Parameters.Clear();
                                trans.Commit();
                            }
                            catch (System.Data.SqlClient.SqlException ex)
                            {
                                trans.Rollback();
                                error = ex.ToString();
                                //此处要注释掉
                                //Log.SaveNote("DBHELPER.QueryErp报错，有参数，SQLTEXT:【" + SQLString + "】，错误：" + ex.ToString());
                                //throw new Exception(ex.Message);
                            }
                            return ds;
                        }
                    }
                }
            }
        }

        /// <summary>
        /// 执行查询语句，返回DataSet
        /// </summary>
        /// <param name="SQLString">查询语句</param>
        /// <returns>DataSet</returns>
        public static DataSet QueryErp(string SQLString, ref string error, params SqlParameter[] cmdParms)
        {
            if (cmdParms == null) return QueryErp(SQLString, ref error);

            if (string.IsNullOrEmpty(SQLString)) return null;
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                if (connection.State != ConnectionState.Open)
                    connection.Open();
                //开始事物
                using (SqlTransaction trans = connection.BeginTransaction())
                {
                    using (SqlCommand cmd = new SqlCommand())
                    {
                        PrepareCommand(cmd, connection, trans, SQLString, cmdParms);
                        using (SqlDataAdapter da = new SqlDataAdapter(cmd))
                        {
                            DataSet ds = new DataSet();
                            try
                            {
                                da.Fill(ds, "ds");
                                cmd.Parameters.Clear();
                                trans.Commit();
                            }
                            catch (System.Data.SqlClient.SqlException ex)
                            {
                                trans.Rollback();
                                error = ex.ToString();
                                //此处要注释掉
                                //Log.SaveNote("DBHELPER.QueryErp报错，有参数，SQLTEXT:【" + SQLString + "】，错误：" + ex.ToString());
                                //throw new Exception(ex.Message);
                            }
                            return ds;
                        }
                    }
                }
            }
        }

        public static DataSet QueryErp(string SQLString,string conStr, ref string error, params SqlParameter[] cmdParms)
        {
            if (cmdParms == null) return QueryErp(SQLString, ref error);

            if (string.IsNullOrEmpty(SQLString)) return null;
            using (SqlConnection connection = new SqlConnection(conStr))
            {
                if (connection.State != ConnectionState.Open)
                    connection.Open();
                //开始事物
                using (SqlTransaction trans = connection.BeginTransaction())
                {
                    using (SqlCommand cmd = new SqlCommand())
                    {
                        PrepareCommand(cmd, connection, trans, SQLString, cmdParms);
                        using (SqlDataAdapter da = new SqlDataAdapter(cmd))
                        {
                            DataSet ds = new DataSet();
                            try
                            {
                                da.Fill(ds, "ds");
                                cmd.Parameters.Clear();
                                trans.Commit();
                            }
                            catch (System.Data.SqlClient.SqlException ex)
                            {
                                trans.Rollback();
                                error = ex.ToString();
                                //此处要注释掉
                                //Log.SaveNote("DBHELPER.QueryErp报错，有参数，SQLTEXT:【" + SQLString + "】，错误：" + ex.ToString());
                                //throw new Exception(ex.Message);
                            }
                            return ds;
                        }
                    }
                }
            }
        }

       
        /// <summary>
        /// 执行查询语句，返回DataSet
        /// </summary>
        /// <param name="SQLString">查询语句</param>
        /// <returns>DataSet</returns>
        public static DataSet Query(string SQLString, params SqlParameter[] cmdParms)
        {
            SQLString = EscapeBusinessString(SQLString);
            if (cmdParms == null) return Query(SQLString);

            if (string.IsNullOrEmpty(SQLString)) return null;
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                SqlCommand cmd = new SqlCommand();
                PrepareCommand(cmd, connection, null, SQLString, cmdParms);
                using (SqlDataAdapter da = new SqlDataAdapter(cmd))
                {
                    DataSet ds = new DataSet();
                    try
                    {
                        da.Fill(ds, "ds");
                        cmd.Parameters.Clear();
                    }
                    catch (System.Data.SqlClient.SqlException ex)
                    {
                        throw new Exception(ex.Message);
                    }
                    return ds;
                }
            }
        }
        public static DataSet Query(string SQLString,string conStr, params SqlParameter[] cmdParms)
        {
            SQLString = EscapeBusinessString(SQLString);
            if (cmdParms == null) return Query(SQLString);

            if (string.IsNullOrEmpty(SQLString)) return null;
            using (SqlConnection connection = new SqlConnection(conStr))
            {
                SqlCommand cmd = new SqlCommand();
                PrepareCommand(cmd, connection, null, SQLString, cmdParms);
                using (SqlDataAdapter da = new SqlDataAdapter(cmd))
                {
                    DataSet ds = new DataSet();
                    try
                    {
                        da.Fill(ds, "ds");
                        cmd.Parameters.Clear();
                    }
                    catch (System.Data.SqlClient.SqlException ex)
                    {
                        throw new Exception(ex.Message);
                    }
                    return ds;
                }
            }
        }

        public static DataSet Query(string SQLString, string conStr,out string msg, params SqlParameter[] cmdParms)
        {
            SQLString = EscapeBusinessString(SQLString);
            msg = "";
            if (cmdParms == null) return Query(SQLString);

            if (string.IsNullOrEmpty(SQLString)) return null;
            using (SqlConnection connection = new SqlConnection(conStr))
            {
                SqlCommand cmd = new SqlCommand();
                PrepareCommand(cmd, connection, null, SQLString, cmdParms);
                using (SqlDataAdapter da = new SqlDataAdapter(cmd))
                {
                    DataSet ds = new DataSet();
                    try
                    {
                        da.Fill(ds, "ds");
                        cmd.Parameters.Clear();
                    }
                    catch (System.Data.SqlClient.SqlException ex)
                    {
                        msg =ex.Message;
                    }
                    return ds;
                }
            }
        }
        public static DataSet QueryCrm(string SQLString, params SqlParameter[] cmdParms)
        {
            string errorMsg = "";
            return runQueryCrm(SQLString, ref errorMsg, cmdParms);
        }
        public static DataSet runQueryCrm(string SQLString, ref string errorMsg, params SqlParameter[] cmdParms)
        {
            if (cmdParms == null) return runQueryCrm(ref errorMsg, SQLString);

            if (string.IsNullOrEmpty(SQLString)) return null;
            using (SqlConnection connection = new SqlConnection(connectionStringCrm))
            {
                SqlCommand cmd = new SqlCommand();
                PrepareCommand(cmd, connection, null, SQLString, cmdParms);
                using (SqlDataAdapter da = new SqlDataAdapter(cmd))
                {
                    DataSet ds = new DataSet();
                    try
                    {
                        da.Fill(ds, "ds");
                    }
                    catch (System.Data.SqlClient.SqlException ex)
                    {
                        //此处要注释掉
                        //Log.SaveNote("DBHELPER.Query报错，有参数，SQLTEXT:【" + SQLString + "】，错误：" + ex.ToString());
                        //throw new Exception(ex.Message);
                        errorMsg = ex.ToString();
                    }
                    finally
                    {
                        cmd.Parameters.Clear();
                        if (da != null) da.Dispose();
                        if (connection != null)
                            connection.Close();
                    }

                    return ds;
                }
            }
        }
        /// <summary>
        /// 执行查询语句，返回DataSet
        /// </summary>
        /// <param name="SQLString">查询语句</param>
        /// <returns>DataSet</returns>
        public static DataSet runQueryCrm(ref string errorMsg, string SQLString)
        {
            if (string.IsNullOrEmpty(SQLString.Trim())) return null;
            using (SqlConnection connection = new SqlConnection(connectionStringCrm))
            {
                DataSet ds = new DataSet();
                try
                {
                    connection.Open();
                    SqlDataAdapter command = new SqlDataAdapter(SQLString, connection);
                    command.Fill(ds, "ds");
                }
                catch (System.Data.SqlClient.SqlException ex)
                {
                    errorMsg = ex.ToString();
                    //此处要注释掉
                    //Log.SaveNote("DBHelper.Query报错，SQLTEXT：【" + SQLString + "】错误是：" + ex.Message);
                    //throw new Exception(ex.Message);
                }
                return ds;
            }

        }
        /// <summary>
        /// 执行查询语句，返回DataSet
        /// </summary>
        /// <param name="SQLString">查询语句</param>
        /// <returns>DataSet</returns>
        public static DataSet Query(string SQLString, List<string[]> sqlparms, ref string msg)
        {
            SQLString = EscapeBusinessString(SQLString);
            if (sqlparms == null || sqlparms.Count == 0)
                return Query(SQLString, ref msg);

            return Query(SQLString, ref msg, sqlparms);
        }
        /// <summary>
        /// 执行查询语句，返回DataSet
        /// </summary>
        /// <param name="SQLString">查询语句</param>
        /// <returns>DataSet</returns>
        public static DataSet Query(string SQLString, ref string msg)
        {
            SQLString = EscapeBusinessString(SQLString);
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                DataSet ds = new DataSet();
                try
                {
                    connection.Open();
                    SqlDataAdapter command = new SqlDataAdapter(SQLString, connection);
                    command.Fill(ds, "ds");
                }
                catch (System.Data.SqlClient.SqlException ex)
                {
                    msg = ex.Message;
                    //throw new Exception(ex.Message);
                    return null;
                }
                return ds;
            }

        }
        public static DataSet Query(string SQLString,string conStr, ref string msg)
        {
            SQLString = EscapeBusinessString(SQLString);
            using (SqlConnection connection = new SqlConnection(conStr))
            {
                DataSet ds = new DataSet();
                try
                {
                    connection.Open();
                    SqlDataAdapter command = new SqlDataAdapter(SQLString, connection);
                    command.Fill(ds, "ds");
                }
                catch (System.Data.SqlClient.SqlException ex)
                {
                    msg = ex.Message;
                    //throw new Exception(ex.Message);
                    return null;
                }
                return ds;
            }

        }
        /// <summary>
        /// 执行查询语句，返回DataSet
        /// </summary>
        /// <param name="SQLString">查询语句</param>
        /// <returns>DataSet</returns>
        public static DataSet Query(string SQLString, ref string msg, List<string[]> sqlparms)
        {
            SQLString = EscapeBusinessString(SQLString);
            try
            {
                using (SqlConnection connection = new SqlConnection(connectionString))
                {
                    SqlCommand cmd = new SqlCommand();
                    PrepareCommand(cmd, connection, null, SQLString, getparms(sqlparms));
                    using (SqlDataAdapter da = new SqlDataAdapter(cmd))
                    {
                        DataSet ds = new DataSet();
                        try
                        {
                            da.Fill(ds, "ds");
                            cmd.Parameters.Clear();
                            return ds;
                        }
                        catch (System.Data.SqlClient.SqlException ex)
                        {
                            msg = ex.Message;
                            //throw new Exception(ex.Message);
                            return null;
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                msg = ex.Message;
                return null;
            }
        }
        public static DataSet Query(string SQLString, ref string msg, List<string[]> sqlparms,string conStr)
        {
            SQLString = EscapeBusinessString(SQLString);
            try
            {
                using (SqlConnection connection = new SqlConnection(conStr))
                {
                    SqlCommand cmd = new SqlCommand();
                    PrepareCommand(cmd, connection, null, SQLString, getparms(sqlparms));
                    using (SqlDataAdapter da = new SqlDataAdapter(cmd))
                    {
                        DataSet ds = new DataSet();
                        try
                        {
                            da.Fill(ds, "ds");
                            cmd.Parameters.Clear();
                            return ds;
                        }
                        catch (System.Data.SqlClient.SqlException ex)
                        {
                            msg = ex.Message;
                            //throw new Exception(ex.Message);
                            return null;
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                msg = ex.Message;
                return null;
            }
        }
        /// <summary>
        /// 2012-2-21新增重载，执行查询语句，返回DataSet
        /// </summary>
        /// <param name="connection">SqlConnection对象</param>
        /// <param name="trans">SqlTransaction事务</param>
        /// <param name="SQLString">查询语句</param>
        /// <returns>DataSet</returns>
        public static DataSet Query(SqlConnection connection, SqlTransaction trans, string SQLString, params SqlParameter[] cmdParms)
        {
            SQLString = EscapeBusinessString(SQLString);
            SqlCommand cmd = new SqlCommand();
            PrepareCommand(cmd, connection, trans, SQLString, cmdParms);
            using (SqlDataAdapter da = new SqlDataAdapter(cmd))
            {
                DataSet ds = new DataSet();
                try
                {
                    da.Fill(ds, "ds");
                    cmd.Parameters.Clear();
                }
                catch (System.Data.SqlClient.SqlException ex)
                {
                    trans.Rollback();
                    throw new Exception(ex.Message);
                }
                return ds;
            }
        }


        private static void PrepareCommand(SqlCommand cmd, SqlConnection conn, SqlTransaction trans, string cmdText, SqlParameter[] cmdParms)
        {
            if (conn.State != ConnectionState.Open)
                conn.Open();
            cmd.Connection = conn;
            cmd.CommandText = cmdText;
            if (trans != null)
                cmd.Transaction = trans;
            cmd.CommandType = CommandType.Text;//cmdType;
            if (cmdParms != null)
            {
                foreach (SqlParameter parameter in cmdParms)
                {
                    if ((parameter.Direction == ParameterDirection.InputOutput || parameter.Direction == ParameterDirection.Input) &&
                        (parameter.Value == null))
                    {
                        parameter.Value = DBNull.Value;
                    }
                    cmd.Parameters.Add(parameter);
                }
            }
        }

        #endregion

        #region 存储过程操作

        /// <summary>
        /// 执行存储过程，返回SqlDataReader ( 注意：调用该方法后，一定要对SqlDataReader进行Close )
        /// </summary>
        /// <param name="storedProcName">存储过程名</param>
        /// <param name="parameters">存储过程参数</param>
        /// <returns>SqlDataReader</returns>
        public static SqlDataReader RunProcedure(string storedProcName, IDataParameter[] parameters)
        {
            foreach (var item in parameters)
            {
                if(item.DbType == DbType.String)
                {
                    item.Value = EscapeBusinessString((string)item.Value);
                }
                
            }
            SqlConnection connection = new SqlConnection(connectionString);
            SqlDataReader returnReader;
            connection.Open();
            SqlCommand command = BuildQueryCommand(connection, storedProcName, parameters);
            command.CommandType = CommandType.StoredProcedure;
            returnReader = command.ExecuteReader(CommandBehavior.CloseConnection);
            return returnReader;

        }
        public static SqlDataReader RunProcedure(string storedProcName, IDataParameter[] parameters,string conStr)
        {
            foreach (var item in parameters)
            {
                if (item.DbType == DbType.String)
                {
                    item.Value = EscapeBusinessString((string)item.Value);
                }

            }
            SqlConnection connection = new SqlConnection(conStr);
            SqlDataReader returnReader;
            connection.Open();
            SqlCommand command = BuildQueryCommand(connection, storedProcName, parameters);
            command.CommandType = CommandType.StoredProcedure;
            returnReader = command.ExecuteReader(CommandBehavior.CloseConnection);
            return returnReader;

        }

        /// <summary>
        /// 获得查询分页数据。
        /// 使用存储过程。
        /// </summary>
        public static DataSet GetRecordByPage(int pageSize, int pageIndex, string strWhere, string fldName, string tblName, string orderBy,string conStr, out int recordCount)
        {
            /*             
--测试
exec UP_GetRecordByPage 1 ,'(select * from [tblQR_SC] where [status]=1) as yy','*','id desc','1=1'
             */
            StringBuilder strSql = new StringBuilder();

            SqlParameter[] parameters = {
					new SqlParameter("@tblName", SqlDbType.VarChar, 5000),
					new SqlParameter("@fldName", SqlDbType.VarChar, 5000),
					new SqlParameter("@PageSize", SqlDbType.Int),
					new SqlParameter("@PageIndex", SqlDbType.Int),
					new SqlParameter("@OrderFiled", SqlDbType.VarChar, 5000),
					new SqlParameter("@strWhere", SqlDbType.VarChar, 5000),
                    new SqlParameter("@ReturnValue",SqlDbType.Int){ Direction = ParameterDirection.Output}
            };
            parameters[0].Value = tblName;
            parameters[1].Value = fldName;
            parameters[2].Value = pageSize;
            parameters[3].Value = pageIndex;
            parameters[4].Value = orderBy;
            parameters[5].Value = strWhere;

            var ds = RunProcedure("UP_GetRecordByPage", parameters, "ds", conStr);
            recordCount = Convert.ToInt32(parameters[6].Value);
            return ds;
        }

        /// <summary>
        /// 执行存储过程
        /// </summary>
        /// <param name="storedProcName">存储过程名</param>
        /// <param name="parameters">存储过程参数</param>
        /// <param name="tableName">DataSet结果中的表名</param>
        /// <returns>DataSet</returns>

        public static DataSet RunProcedure(string storedProcName, IDataParameter[] parameters, string tableName,string conStr)
        {
            foreach (var item in parameters)
            {
                if (item.DbType == DbType.String)
                {
                    item.Value = EscapeBusinessString((string)item.Value);
                }

            }
            using (SqlConnection connection = new SqlConnection(conStr))
            {
                DataSet dataSet = new DataSet();
                connection.Open();
                SqlDataAdapter sqlDA = new SqlDataAdapter();
                sqlDA.SelectCommand = BuildQueryCommand(connection, storedProcName, parameters);
                sqlDA.Fill(dataSet, tableName);
                connection.Close();
                return dataSet;
            }
        }
        public static DataSet RunProcedure(string storedProcName, IDataParameter[] parameters, string tableName, int Times)
        {
            foreach (var item in parameters)
            {
                if (item.DbType == DbType.String)
                {
                    item.Value = EscapeBusinessString((string)item.Value);
                }

            }
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                DataSet dataSet = new DataSet();
                connection.Open();
                SqlDataAdapter sqlDA = new SqlDataAdapter();
                sqlDA.SelectCommand = BuildQueryCommand(connection, storedProcName, parameters);
                sqlDA.SelectCommand.CommandTimeout = Times;
                sqlDA.Fill(dataSet, tableName);
                connection.Close();
                return dataSet;
            }
        }


        /// <summary>
        /// 构建 SqlCommand 对象(用来返回一个结果集，而不是一个整数值)
        /// </summary>
        /// <param name="connection">数据库连接</param>
        /// <param name="storedProcName">存储过程名</param>
        /// <param name="parameters">存储过程参数</param>
        /// <returns>SqlCommand</returns>
        private static SqlCommand BuildQueryCommand(SqlConnection connection, string storedProcName, IDataParameter[] parameters)
        {
            SqlCommand command = new SqlCommand(storedProcName, connection);
            command.CommandType = CommandType.StoredProcedure;
            command.CommandTimeout = 60; // 超时时间。
            foreach (SqlParameter parameter in parameters)
            {
                if (parameter != null)
                {
                    // 检查未分配值的输出参数,将其分配以DBNull.Value.
                    if ((parameter.Direction == ParameterDirection.InputOutput || parameter.Direction == ParameterDirection.Input) &&
                        (parameter.Value == null))
                    {
                        parameter.Value = DBNull.Value;
                    }
                    command.Parameters.Add(parameter);
                }
            }

            return command;
        }

        /// <summary>
        /// 执行存储过程，返回影响的行数		
        /// </summary>
        /// <param name="storedProcName">存储过程名</param>
        /// <param name="parameters">存储过程参数</param>
        /// <param name="rowsAffected">影响的行数</param>
        /// <returns></returns>
        public static int RunProcedure(string storedProcName, IDataParameter[] parameters, out int rowsAffected)
        {
            foreach (var item in parameters)
            {
                if (item.DbType == DbType.String)
                {
                    item.Value = EscapeBusinessString((string)item.Value);
                }

            }
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                int result;
                connection.Open();
                SqlCommand command = BuildIntCommand(connection, storedProcName, parameters);
                rowsAffected = command.ExecuteNonQuery();
                result = (int)command.Parameters["ReturnValue"].Value;
                //Connection.Close();
                return result;
            }
        }
        public static int RunProcedure(string storedProcName, IDataParameter[] parameters,string conStr, out int rowsAffected)
        {
            foreach (var item in parameters)
            {
                if (item.DbType == DbType.String)
                {
                    item.Value = EscapeBusinessString((string)item.Value);
                }

            }
            using (SqlConnection connection = new SqlConnection(conStr))
            {
                int result;
                connection.Open();
                SqlCommand command = BuildIntCommand(connection, storedProcName, parameters);
                rowsAffected = command.ExecuteNonQuery();
                result = (int)command.Parameters["ReturnValue"].Value;
                //Connection.Close();
                return result;
            }
        }

        /// <summary>
        /// 创建 SqlCommand 对象实例(用来返回一个整数值)	
        /// </summary>
        /// <param name="storedProcName">存储过程名</param>
        /// <param name="parameters">存储过程参数</param>
        /// <returns>SqlCommand 对象实例</returns>
        private static SqlCommand BuildIntCommand(SqlConnection connection, string storedProcName, IDataParameter[] parameters)
        {
            SqlCommand command = BuildQueryCommand(connection, storedProcName, parameters);
            command.Parameters.Add(new SqlParameter("ReturnValue",
                SqlDbType.Int, 4, ParameterDirection.ReturnValue,
                false, 0, 0, string.Empty, DataRowVersion.Default, null));
            return command;
        }
        #endregion


        #region 数据类型相关操作
        /// <summary>
        /// SQL数据类型相关操作
        /// </summary>
        public static Dictionary<string, SqlDbType> getStringToSqlDbType()
        {
            #region C#类型转SQL数据类型集合
            Dictionary<string, SqlDbType> typeMap = new Dictionary<string, SqlDbType>();
            typeMap["nvarchar"] = SqlDbType.NVarChar;
            typeMap["int"] = SqlDbType.Int;
            typeMap["bigint"] = SqlDbType.BigInt;
            typeMap["binary"] = SqlDbType.Binary;
            typeMap["bit"] = SqlDbType.Bit;
            typeMap["char"] = SqlDbType.Char;
            typeMap["datetime"] = SqlDbType.DateTime;
            typeMap["text"] = SqlDbType.Text;
            #endregion
            return typeMap;
        }

        /// <summary>
        /// SQL数据类型相关操作
        /// </summary>
        public static Dictionary<Type, SqlDbType> getSqlDbType()
        {
            #region C#类型转SQL数据类型集合
            Dictionary<Type, SqlDbType> typeMap = new Dictionary<Type, SqlDbType>();
            typeMap[typeof(Int32)] = SqlDbType.Int;
            typeMap[typeof(bool)] = SqlDbType.Bit;
            typeMap[typeof(byte)] = SqlDbType.TinyInt;
            typeMap[typeof(sbyte)] = SqlDbType.TinyInt;
            typeMap[typeof(short)] = SqlDbType.SmallInt;
            typeMap[typeof(ushort)] = SqlDbType.SmallInt;
            typeMap[typeof(uint)] = SqlDbType.Int;
            typeMap[typeof(long)] = SqlDbType.BigInt;
            typeMap[typeof(ulong)] = SqlDbType.BigInt;
            typeMap[typeof(decimal)] = SqlDbType.Decimal;
            typeMap[typeof(float)] = SqlDbType.Real;
            typeMap[typeof(double)] = SqlDbType.Float;
            typeMap[typeof(string)] = SqlDbType.NVarChar;
            typeMap[typeof(DateTime)] = SqlDbType.DateTime;
            typeMap[typeof(Guid)] = SqlDbType.UniqueIdentifier;
            typeMap[typeof(byte[])] = SqlDbType.VarBinary;
            #endregion
            return typeMap;
        }

        // sql server数据类型（如：varchar）
        // 转换为SqlDbType类型
        public static SqlDbType SqlTypeString2SqlType(string sqlTypeString)
        {
            SqlDbType dbType = SqlDbType.Variant;//默认为Object
            switch (sqlTypeString.ToLower())
            {
                case "int":
                    dbType = SqlDbType.Int;
                    break;
                case "varchar":
                    dbType = SqlDbType.VarChar;
                    break;
                case "bit":
                    dbType = SqlDbType.Bit;
                    break;
                case "datetime":
                    dbType = SqlDbType.DateTime;
                    break;
                case "decimal":
                    dbType = SqlDbType.Decimal;
                    break;
                case "float":
                    dbType = SqlDbType.Float;
                    break;
                case "image":
                    dbType = SqlDbType.Image;
                    break;
                case "money":
                    dbType = SqlDbType.Money;
                    break;
                case "ntext":
                    dbType = SqlDbType.NText;
                    break;
                case "nvarchar":
                    dbType = SqlDbType.NVarChar;
                    break;
                case "smalldatetime":
                    dbType = SqlDbType.SmallDateTime;
                    break;
                case "smallint":
                    dbType = SqlDbType.SmallInt;
                    break;
                case "text":
                    dbType = SqlDbType.Text;
                    break;
                case "bigint":
                    dbType = SqlDbType.BigInt;
                    break;
                case "binary":
                    dbType = SqlDbType.Binary;
                    break;
                case "char":
                    dbType = SqlDbType.Char;
                    break;
                case "nchar":
                    dbType = SqlDbType.NChar;
                    break;
                case "numeric":
                    dbType = SqlDbType.Decimal;
                    break;
                case "real":
                    dbType = SqlDbType.Real;
                    break;
                case "smallmoney":
                    dbType = SqlDbType.SmallMoney;
                    break;
                case "sql_variant":
                    dbType = SqlDbType.Variant;
                    break;
                case "timestamp":
                    dbType = SqlDbType.Timestamp;
                    break;
                case "tinyint":
                    dbType = SqlDbType.TinyInt;
                    break;
                case "uniqueidentifier":
                    dbType = SqlDbType.UniqueIdentifier;
                    break;
                case "varbinary":
                    dbType = SqlDbType.VarBinary;
                    break;
                case "xml":
                    dbType = SqlDbType.Xml;
                    break;
            }
            return dbType;
        }

        /// <summary>
        /// 对应类型返回对应值
        /// </summary>
        /// <param name="sqlTypeString"></param>
        /// <param name="sqlValue"></param>
        /// <returns></returns>
        public static object SqlTypeString2SqlValue(string sqlTypeString, string sqlValue)
        {
            object obj = sqlValue;
            switch (sqlTypeString.ToLower())
            {
                case "int":
                    obj = DataConverter.ToInt32(sqlValue);// SqlDbType.Int;
                    break;
                case "varchar":
                    obj = sqlValue;
                    break;
                case "bit":
                    obj = DataConverter.ToBoolean(sqlValue);
                    break;
                case "datetime":
                    obj = DataConverter.ToDate(sqlValue);
                    break;
                case "decimal":
                    obj = DataConverter.ToDecimal(sqlValue);
                    break;
                case "float":
                    obj = DataConverter.ToDecimal(sqlValue);
                    break;
                case "image":
                    obj = sqlValue;
                    break;
                case "money":
                    obj = DataConverter.ToDecimal(sqlValue);
                    break;
                case "ntext":
                    obj = sqlValue;
                    break;
                case "nvarchar":
                    obj = sqlValue;
                    break;
                case "smalldatetime":
                    obj = DataConverter.ToDate(sqlValue);
                    break;
                case "smallint":
                    obj = DataConverter.ToInt32(sqlValue);
                    break;
                case "text":
                    obj = sqlValue;
                    break;
                case "bigint":
                    obj = DataConverter.ToLong(sqlValue);
                    break;
                case "binary":
                    obj = sqlValue;
                    break;
                case "char":
                    obj = sqlValue;
                    break;
                case "nchar":
                    obj = sqlValue;
                    break;
                case "numeric":
                    obj = DataConverter.ToDecimal(sqlValue);
                    break;
                case "real":
                    obj = sqlValue;
                    break;
                case "smallmoney":
                    obj = DataConverter.ToDecimal(sqlValue);
                    break;
                case "sql_variant":
                    obj = sqlValue;
                    break;
                case "timestamp":
                    obj = sqlValue;
                    break;
                case "tinyint":
                    obj = DataConverter.ToInt32(sqlValue);
                    break;
                case "uniqueidentifier":
                    obj = sqlValue;
                    break;
                case "varbinary":
                    obj = sqlValue;
                    break;
                case "xml":
                    obj = sqlValue;
                    break;
            }
            return obj;
        }

        // SqlDbType转换为C#数据类型
        public static Type SqlType2CsharpType(SqlDbType sqlType)
        {
            switch (sqlType)
            {
                case SqlDbType.BigInt:
                    return typeof(Int64);
                case SqlDbType.Binary:
                    return typeof(Object);
                case SqlDbType.Bit:
                    return typeof(Boolean);
                case SqlDbType.Char:
                    return typeof(String);
                case SqlDbType.DateTime:
                    return typeof(DateTime);
                case SqlDbType.Decimal:
                    return typeof(Decimal);
                case SqlDbType.Float:
                    return typeof(Double);
                case SqlDbType.Image:
                    return typeof(Object);
                case SqlDbType.Int:
                    return typeof(Int32);
                case SqlDbType.Money:
                    return typeof(Decimal);
                case SqlDbType.NChar:
                    return typeof(String);
                case SqlDbType.NText:
                    return typeof(String);
                case SqlDbType.NVarChar:
                    return typeof(String);
                case SqlDbType.Real:
                    return typeof(Single);
                case SqlDbType.SmallDateTime:
                    return typeof(DateTime);
                case SqlDbType.SmallInt:
                    return typeof(Int16);
                case SqlDbType.SmallMoney:
                    return typeof(Decimal);
                case SqlDbType.Text:
                    return typeof(String);
                case SqlDbType.Timestamp:
                    return typeof(Object);
                case SqlDbType.TinyInt:
                    return typeof(Byte);
                case SqlDbType.Udt://自定义的数据类型
                    return typeof(Object);
                case SqlDbType.UniqueIdentifier:
                    return typeof(Object);
                case SqlDbType.VarBinary:
                    return typeof(Object);
                case SqlDbType.VarChar:
                    return typeof(String);
                case SqlDbType.Variant:
                    return typeof(Object);
                case SqlDbType.Xml:
                    return typeof(Object);
                default:
                    return null;
            }
        }
        #endregion

        #region 获取参数
        /// <summary>
        /// 获取参数
        /// </summary>
        /// <param name="dr"></param>
        /// <param name="sqlparms">0:参数名， 1：参数类型 。2：参数值</param>
        /// <returns></returns>
        public static SqlParameter[] getparms(List<string[]> sqlparms)
        {
            if (sqlparms == null) return null;
            //参数集合
            List<SqlParameter> parameters = new List<SqlParameter>();
            //遍历参数名
            for (int i = 0; i < sqlparms.Count; i++)
            {
                //参数名是否为空
                if (sqlparms[i][0].Trim() == "") continue;
                //添加有效参数
                parameters.Add(new SqlParameter(sqlparms[i][0], SqlTypeString2SqlType(sqlparms[i][1])));
            }
            //
            SqlParameter[] SqlParameters = parameters.ToArray();
            //遍历参数名
            for (int i = 0; i < sqlparms.Count; i++)
            {
                //参数名是否为空
                if (sqlparms[i][0].Trim() == "") continue;
                //填充对应的值
                SqlParameters[i].Value = SqlTypeString2SqlValue(sqlparms[i][1], sqlparms[i][2]);
            }
            return SqlParameters;
        }

        /// <summary>
        /// 获取参数
        /// </summary>
        /// <param name="dr"></param>
        /// <param name="sqlValues"></param>
        /// <returns></returns>
        public static SqlParameter[] getparms(DataRow dr, params object[] sqlValues)
        {
            //是否存入缓存，1：存入，0：不存入
            string iscache = dr["iscache"].ToString();
            string cachetime = dr["cachetime"].ToString();

            //参数名称
            string[] sqlparms_name = dr["sqlparms_name"].ToString().Split(',');
            //参数类型
            string[] sqlparms_type = dr["sqlparms_type"].ToString().Split(',');
            //需要执行的sql
            string sqlstr = dr["sqlstr"].ToString();
            //SQL数据类型相关操作
            Dictionary<string, SqlDbType> tt = getStringToSqlDbType();

            //参数集合
            List<SqlParameter> parameters = new List<SqlParameter>();
            //遍历参数名
            for (int i = 0; i < sqlparms_name.Length; i++)
            {
                //参数名是否为空
                if (sqlparms_name[i].Trim() == "") continue;
                //添加有效参数
                parameters.Add(new SqlParameter(sqlparms_name[i], tt[sqlparms_type[i]]));
            }
            //
            SqlParameter[] SqlParameters = parameters.ToArray();
            //遍历参数名
            for (int i = 0; i < sqlparms_name.Length; i++)
            {
                //参数名是否为空
                if (sqlparms_name[i].Trim() == "") continue;
                //填充对应的值
                SqlParameters[i].Value = sqlValues[i];
            }
            return SqlParameters;
        }
        #endregion
        #region Insert
        /// <summary>
        /// 大批量添加数据ds.Tables[0] 表名strTblName(数据库表名称)
        /// </summary>
        /// <param name="ds"></param>
        /// <param name="strTblName"></param>
        /// <returns>受影响的行数</returns>
        public static int SqlBulkInsert(DataTable dt, string strTblName)
        {
            SqlConnection conn = new SqlConnection(connectionString);
            int affect = 0;
            try
            {
                SqlCommand myCommand = new SqlCommand("select * from " + strTblName, conn);
                SqlDataAdapter myAdapter = new SqlDataAdapter(myCommand);
                SqlCommandBuilder myCommandBuilder = new SqlCommandBuilder(myAdapter);
                myAdapter.InsertCommand = myCommandBuilder.GetInsertCommand();
                myAdapter.ContinueUpdateOnError = true;
                foreach (DataRow dr in dt.Rows)
                {
                    if (dr.RowState != DataRowState.Added)
                        dr.SetAdded();
                }
                conn.Open();
                affect = myAdapter.Update(dt);
                // ds.AcceptChanges();
                conn.Close();
                // return ds; 
            }
            catch (Exception err)
            {
                conn.Close();
                throw err;
            }
            return affect;
        }
        public static int SqlBulkInsert(DataTable dt, string strTblName,string conStr)
        {
            SqlConnection conn = new SqlConnection(conStr);
            int affect = 0;
            try
            {
                SqlCommand myCommand = new SqlCommand("select * from " + strTblName, conn);
                SqlDataAdapter myAdapter = new SqlDataAdapter(myCommand);
                SqlCommandBuilder myCommandBuilder = new SqlCommandBuilder(myAdapter);
                myAdapter.InsertCommand = myCommandBuilder.GetInsertCommand();
                myAdapter.ContinueUpdateOnError = true;
                foreach (DataRow dr in dt.Rows)
                {
                    if (dr.RowState != DataRowState.Added)
                        dr.SetAdded();
                }
                conn.Open();
                affect = myAdapter.Update(dt);
                // ds.AcceptChanges();
                conn.Close();
                // return ds; 
            }
            catch (Exception err)
            {
                conn.Close();
                throw err;
            }
            return affect;
        }
        #endregion

        #region Update
        /// <summary>
        /// 大批量更改数据ds.Tables[0] 表名strTblName(数据库表名称,表需要有主键)
        /// </summary>
        /// <param name="ds"></param>
        /// <param name="strTblName"></param>
        /// <returns>受影响的行数</returns>
        public static int SqlBulkUpdate(DataTable dt, string strTblName)
        {
            SqlConnection conn = new SqlConnection(connectionString);
            int affect = 0;
            try
            {
                //foreach (DataRow dr in dt.Rows)
                //{
                //    if (dr.RowState == DataRowState.Added)
                //    {
                //        dr[0] = dr[0];
                //        dr.AcceptChanges();
                //        dr.SetModified();
                //    }
                //}           


                SqlCommand myCommand = new SqlCommand("select * from " + strTblName, conn);
                SqlDataAdapter myAdapter = new SqlDataAdapter(myCommand);
                SqlCommandBuilder myCommandBuilder = new SqlCommandBuilder(myAdapter);
                myAdapter.UpdateCommand = myCommandBuilder.GetUpdateCommand();
                myAdapter.ContinueUpdateOnError = true;
                conn.Open();
                //SqlTransaction sqltra = conn.BeginTransaction();//开始事务
                //myCommand.Transaction = sqltra;//，在执行SQL时，
                //try
                //{
                affect = myAdapter.Update(dt);
                //}
                //catch (Exception ex) { }
                //sqltra.Commit();
                // ds.AcceptChanges();
                conn.Close();
                // return ds;    //数据集的行状态在更新后会都变为: UnChange,在这次更新后客户端要用返回的ds 
            }
            catch (Exception err)
            {
                conn.Close();
                throw err;
            }
            return affect;
        }
        public static int SqlBulkUpdate(DataTable dt, string strTblName,string conStr)
        {
            SqlConnection conn = new SqlConnection(conStr);
            int affect = 0;
            try
            {
                //foreach (DataRow dr in dt.Rows)
                //{
                //    if (dr.RowState == DataRowState.Added)
                //    {
                //        dr[0] = dr[0];
                //        dr.AcceptChanges();
                //        dr.SetModified();
                //    }
                //}           


                SqlCommand myCommand = new SqlCommand("select * from " + strTblName, conn);
                SqlDataAdapter myAdapter = new SqlDataAdapter(myCommand);
                SqlCommandBuilder myCommandBuilder = new SqlCommandBuilder(myAdapter);
                myAdapter.UpdateCommand = myCommandBuilder.GetUpdateCommand();
                myAdapter.ContinueUpdateOnError = true;
                conn.Open();
                //SqlTransaction sqltra = conn.BeginTransaction();//开始事务
                //myCommand.Transaction = sqltra;//，在执行SQL时，
                //try
                //{
                affect = myAdapter.Update(dt);
                //}
                //catch (Exception ex) { }
                //sqltra.Commit();
                // ds.AcceptChanges();
                conn.Close();
                // return ds;    //数据集的行状态在更新后会都变为: UnChange,在这次更新后客户端要用返回的ds 
            }
            catch (Exception err)
            {
                conn.Close();
                throw err;
            }
            return affect;
        }

        /// <summary>
        /// 大批量更改数据ds.Tables[0] 表名strTblName(数据库表名称,表需要有主键)
        /// </summary>
        /// <param name="ds"></param>
        /// <param name="strTblName"></param>
        /// <returns>受影响的行数</returns>
        public static int SqlBulkUpdate(DataTable dt, string strTblName, SqlConnection conn, SqlCommand updatecmd)
        {
            int affect = 0;
            try
            {
                //foreach (DataRow dr in dt.Rows)
                //{
                //    if (dr.RowState == DataRowState.Added)
                //    {
                //        dr[0] = dr[0];
                //        dr.AcceptChanges();
                //        dr.SetModified();
                //    }
                //}           

                SqlCommand myCommand = new SqlCommand("select * from " + strTblName, conn);
                SqlDataAdapter myAdapter = new SqlDataAdapter(myCommand);
                SqlCommandBuilder myCommandBuilder = new SqlCommandBuilder(myAdapter);
                myAdapter.UpdateCommand = updatecmd;
                myAdapter.ContinueUpdateOnError = true;
                conn.Open();
                affect = myAdapter.Update(dt);
                // ds.AcceptChanges();
                conn.Close();
                // return ds;    //数据集的行状态在更新后会都变为: UnChange,在这次更新后客户端要用返回的ds 
            }
            catch (Exception err)
            {
                conn.Close();
                throw err;
            }
            return affect;
        }
        #endregion


        #region 各业务自带的转义字符转换

        public static string EscapeBusinessString(string businessStr)
        {
            return businessStr;
        }

        #endregion


    }
}
